using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using System.Globalization;
using Smo = Microsoft.SqlServer.Management.Smo;

using SqlWebAdmin.CSharp;

public partial class EditColumn : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // If not Globals PostBack
        if( ! Page.IsPostBack )
        {
               lblError.Visible = true;

                // Create Globals new instance of the SMO Server class
				Smo.Server smoServer = SmoHelper.GetServer();
                try
                { 
                   // Get the SMO Database 
                    String databaseName = Request.QueryString["database"];
                    Smo.Database smoDatabase = smoServer.Databases[databaseName];
                    if( smoDatabase != null )
                    {

                        // Get the table and column names from the URL
                        String tableName = Request.QueryString["table"];
                        String tableSchema = Request.QueryString["schema"];
                        String columnName = Request.QueryString["column"];

                        // If column isn't specified in request, then we are creating Globals new column
                        if( columnName == null || columnName.Length == 0 )
                        {

                            // Let the user know we are adding Globals new column
                            lblTitle.Text = "Add Column";
                            cmdUpdate.Text = "Save";

                            // Use Globals default column name
                            int i = 1;
                            columnName = "Column" + i.ToString();

                            // If this is an existing Table
                            if( smoDatabase.Tables.Contains(tableName) ) 
                            {

                                Smo.Table smoTable = smoDatabase.Tables[tableName, tableSchema];

                                // Create Globals column name that doesn't exist in Table 
                                while( smoTable.Columns.Contains(columnName) ) {
                                    i += 1;
                                    columnName = "Column" + i.ToString(System.Globalization.CultureInfo.CurrentUICulture);
                                }
                            }

                            // Initialize controls with default values
                            chkIsPrimaryKey.Checked = true;
                            txtColumnName.Text = columnName;
                            ddlDataType.SelectedIndex = ddlDataType.Items.IndexOf(new ListItem("char"));
                            txtLength.Text = "10";
                            chkIsNullable.Checked = true;
                            txtDefaultValue.Text = String.Empty;
                            txtPrecision.Text = "0";
                            txtScale.Text = "0";
                            chkIsIdentity.Checked = true;
                            txtIdentitySeed.Text = "1";
                            txtIdentityIncrement.Text = "1";
                            chkIsRowGuid.Checked = true;
                        } 
                        else 
                        {
                            // Let the user know we are editing an existing column
                            lblTitle.Text = "Edit Column";
                            cmdUpdate.Text = "Save";

                            // Get the Table
                            Smo.Table smoTable = smoDatabase.Tables[tableName, tableSchema];
                            if( smoTable == null ) 
                            {
                                Response.Redirect(String.Format("error.aspx?error={0}", Globals.tableNotFoundErrorNumber), true);
                            } 
                            else 
                            {
                                // Get the Column
                                Smo.Column smoColumn = smoTable.Columns[columnName];
                                if( smoColumn == null )
                                {
                                    Response.Redirect(String.Format("error.aspx?error={0}", Globals.columnNotFoundErrorNumber), true);
                                } 
                                else 
                                {
                                    // Initialize column editor
                                    chkIsPrimaryKey.Checked = smoColumn.InPrimaryKey;
                                    txtColumnName.Text = smoColumn.Name;
                                    ddlDataType.SelectedValue = smoColumn.DataType.Name;
                                    txtLength.Text = smoColumn.DataType.MaximumLength.ToString(System.Globalization.CultureInfo.CurrentUICulture);
                                    chkIsNullable.Checked = smoColumn.Nullable;
                                    txtDefaultValue.Text = smoColumn.Default;
                                    txtPrecision.Text = smoColumn.DataType.NumericPrecision.ToString(System.Globalization.CultureInfo.CurrentUICulture);
                                    txtScale.Text = smoColumn.DataType.NumericScale.ToString(System.Globalization.CultureInfo.CurrentUICulture);
                                    chkIsIdentity.Checked = smoColumn.Identity;
                                    if( chkIsIdentity.Checked )
                                    {
                                        txtIdentitySeed.Text = smoColumn.IdentitySeed.ToString(System.Globalization.CultureInfo.CurrentUICulture);
                                        txtIdentityIncrement.Text = smoColumn.IdentityIncrement.ToString(System.Globalization.CultureInfo.CurrentUICulture);
                                    } 
                                    else 
                                    {
                                        txtIdentitySeed.Text = "1";
                                        txtIdentityIncrement.Text = "1";
                                    }
                                    chkIsRowGuid.Checked = smoColumn.RowGuidCol;

                                    // Since we are editing an existing column, the table will be recreated,
                                    // so we must warn about data loss
                                    lblDataLossWarning.Visible = true;
                                }
                            }
                        }
                    }
                    else
                    {
                        Response.Redirect(String.Format("error.aspx?error={0}", Globals.databaseNotFoundErrorNumber), true);
                    }
                }
                catch( Microsoft.SqlServer.Management.Smo.SmoException smoEx ) {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(smoEx));
                    lblError.Visible = true;
                }
                catch( System.Exception ex ) {
                    lblError.Visible = true;
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(ex));
                }
            
        }
    }

    protected void cmdUpdate_Click( Object sender, EventArgs e )
    {
        // If the page is valid
        if( Page.IsValid )
        {
            
                lblError.Visible = true;

                // Create a new instance of the SMO Server class
				Smo.Server smoServer = SmoHelper.GetServer();
                try
                {
                    

                    // Get the SMO Database 
                    String  databaseName  = Request.QueryString["database"];
                    Smo.Database  smoDatabase  = smoServer.Databases[databaseName];
                    if( smoDatabase != null )
                    {
                        // Get the Table
                        String tableName = Request.QueryString["table"];
                        String tableSchema = Request.QueryString["schema"];
                        Smo.Table smoTable = smoDatabase.Tables[tableName, tableSchema];

                        // If the Table doesn't exist
                        if( smoTable == null )
                        {
                            // Add a new Table and Column
                            smoTable = new Smo.Table(smoDatabase, tableName);
                            String columnName  = txtColumnName.Text;

                            Smo.Column smoColumn = new Smo.Column(smoTable, columnName);
                            smoTable.Columns.Add(smoColumn);

                            SetColumnAttributes(smoColumn, smoTable);
                            smoTable.Create();
                        }
                        else
                        {
                            // Get the original Table name form the URL
                            String  originalColumnName  = Request.QueryString["column"];
                            String  columnName  = txtColumnName.Text;

                            // If original name is blank
                            if( originalColumnName == null || originalColumnName.Length == 0 )
                            {

                                // Add a new Column to the existing Table
                                Smo.Column smoColumn = new Smo.Column(smoTable, columnName);
                                smoTable.Columns.Add(smoColumn);
                                SetColumnAttributes(smoColumn, smoTable);
                                smoColumn.Create();
                            }
                            else
                            {
                                // Edit the existing Column in the existing Table
                                Smo.Column smoColumn = smoTable.Columns[columnName];
                                SetColumnAttributes(smoColumn, smoTable);
                                smoColumn.Alter();
                            }
                        }

                        // Return back to the columns page
                        Response.Redirect(String.Format("columns.aspx?database={0}&table={1}&schema={2}", 
                            HttpUtility.UrlEncode(Request.QueryString["database"]), 
                            HttpUtility.UrlEncode(Request.QueryString["table"]), 
                            HttpUtility.UrlEncode(Request.QueryString["schema"])), true);
                    }
                    else
                    {
                        Response.Redirect(String.Format("error.aspx?error={0}", Globals.databaseNotFoundErrorNumber), true);
                    }
                }
                catch( Microsoft.SqlServer.Management.Smo.SmoException smoEx )
                {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(smoEx));
                    lblError.Visible = true;
                }
                catch( System.Exception ex )
                {
                    lblError.Visible = true;
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(ex));
                }
            
        }
    }

    protected void cmdCancel_Click( Object sender, EventArgs e )
    {
        // Just redirect back to columns list
        Response.Redirect(String.Format("Columns.aspx?database={0}&table={1}&schema={2}", 
            Server.UrlEncode(Request.QueryString["database"]), 
            Server.UrlEncode(Request.QueryString["table"]), 
            Server.UrlEncode(Request.QueryString["schema"])), true);
    }

    private void DataTypeError()
    {
        Response.Redirect(String.Format("error.aspx?error={0}", Globals.invalidDataTypeSetting), true);
    }

    private void SetColumnAttributes( Smo.Column smoColumn, Smo.Table smoTable )
    {
        switch( ddlDataType.SelectedItem.Value )
        {
            case "bigint":
                smoColumn.DataType = Smo.DataType.BigInt;
                break;
            case "binary":
                smoColumn.DataType = Smo.DataType.Binary(Int32.Parse(txtLength.Text));
                break;
            case "bit":
                smoColumn.DataType = Smo.DataType.Bit;
                break;
            case "char":
                smoColumn.DataType = Smo.DataType.Char(Int32.Parse(txtLength.Text));
                break;
            case "datetime":
                smoColumn.DataType = Smo.DataType.DateTime;
                break;
            case "decimal":
                smoColumn.DataType = Smo.DataType.Decimal(Byte.Parse(txtPrecision.Text), Byte.Parse(txtScale.Text));
                break;
            case "float":
                smoColumn.DataType = Smo.DataType.Float;
                break;
            case "image":
                smoColumn.DataType = Smo.DataType.Image;
                break;
            case "int":
                smoColumn.DataType = Smo.DataType.Int;
                break;
            case "money":
                smoColumn.DataType = Smo.DataType.Money;
                break;
            case "nchar":
                smoColumn.DataType = Smo.DataType.NChar(Int32.Parse(txtLength.Text));
                break;
            case "ntext":
                smoColumn.DataType = Smo.DataType.NText;
                break;
            case "numeric":
                smoColumn.DataType = Smo.DataType.Decimal(Byte.Parse(txtPrecision.Text), Byte.Parse(txtScale.Text));
                break;
            case "nvarchar":
                smoColumn.DataType = Smo.DataType.NVarChar(Int32.Parse(txtLength.Text));
                break;
            case "nvarchar(max)":
                smoColumn.DataType = Smo.DataType.NVarCharMax;
                break;
            case "real":
                smoColumn.DataType = Smo.DataType.Real;
                break;
            case "smalldatetime":
                smoColumn.DataType = Smo.DataType.SmallDateTime;
                break;
            case "smallint":
                smoColumn.DataType = Smo.DataType.SmallInt;
                break;
            case "smallmoney":
                smoColumn.DataType = Smo.DataType.SmallMoney;
                break;
            case "sql_variant":
                smoColumn.DataType = Smo.DataType.Variant;
                break;
            case "text":
                smoColumn.DataType = Smo.DataType.Text;
                break;
            case "timestamp":
                smoColumn.DataType = Smo.DataType.Timestamp;
                break;
            case "tinyint":
                smoColumn.DataType = Smo.DataType.TinyInt;
                break;
            case "uniqueidentifier":
                smoColumn.DataType = Smo.DataType.UniqueIdentifier;
                break;
            case "userdefineddatatype":
                smoColumn.DataType = Smo.DataType.UserDefinedDataType(this.txtTypeName.Text, this.txtTypeSchema.Text);
                break;
            case "userdefinedtype":
                smoColumn.DataType = Smo.DataType.UserDefinedType(this.txtTypeName.Text, this.txtTypeSchema.Text);
                break;
            case "varbinary":
                smoColumn.DataType = Smo.DataType.VarBinary(Int32.Parse(txtLength.Text));
                break;
            case "varchar":
                smoColumn.DataType = Smo.DataType.VarChar(Int32.Parse(txtLength.Text));
                break;
            case "varchar(max)":
                smoColumn.DataType = Smo.DataType.VarCharMax;
                break;
            case "xml":
                if( this.txtTypeName.Text.Trim().Length > 0 )
                {
                    smoColumn.DataType = Smo.DataType.Xml(this.txtTypeName.Text);
                }
                else
                {
                    smoColumn.DataType = new Smo.DataType();
                    smoColumn.DataType.SqlDataType = Microsoft.SqlServer.Management.Smo.SqlDataType.Xml;
                }
                break;
            default:
                Response.Redirect(String.Format("error.aspx?error={0}", Globals.unknownDataType), true);
                break;
        }


        if( smoColumn.State == Smo.SqlSmoState.Creating )
        {
            smoColumn.Nullable = chkIsNullable.Checked;
            smoColumn.Identity = chkIsIdentity.Checked;
            smoColumn.RowGuidCol = chkIsRowGuid.Checked;
        }
        else
        {
            // Cannot change if column exists
            if( smoColumn.Nullable != chkIsNullable.Checked )
            {
                smoColumn.Nullable = chkIsNullable.Checked;
            }

            if( smoColumn.Identity != chkIsIdentity.Checked )
            {
                smoColumn.Identity = chkIsIdentity.Checked;
            }

            if( smoColumn.RowGuidCol != chkIsRowGuid.Checked ) 
            {
                smoColumn.RowGuidCol = chkIsRowGuid.Checked;
            }
        }

        if( smoColumn.Identity )
        {
            if( txtIdentitySeed.Text.Trim().Length > 0 )
            {
                smoColumn.IdentitySeed = Int32.Parse(txtIdentitySeed.Text);
            }

            if( txtIdentityIncrement.Text.Trim().Length > 0 )
            {
                smoColumn.IdentityIncrement = Int32.Parse(txtIdentityIncrement.Text);
            }
        }

        if (txtDefaultValue.Text.Trim().Length > 0 )
        {
            smoColumn.Default = txtDefaultValue.Text;
        }

        if( chkIsPrimaryKey.Checked ) 
        {
            Smo.Index smoIndex = new Smo.Index(smoTable, "PK_" + smoTable.Name);
            Smo.IndexedColumn idxCol = new Smo.IndexedColumn(smoIndex, smoColumn.Name);
            smoIndex.IndexedColumns.Add(idxCol);
            smoIndex.IsClustered = true;
            smoIndex.IsUnique = true;
            smoIndex.IndexKeyType = Smo.IndexKeyType.DriPrimaryKey;
            smoTable.Indexes.Add(smoIndex);
        }

    }
}
